package hezi.helper;

import hezi.core.Application;
import hezi.core.Image;
import hezi.core.Post;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import dz.common.PropTool;

public class ImageHelperDBHelper {

	public synchronized static void creaetableIfNotExist(Connection conn) {
		try {
			Statement stat = conn.createStatement();
			if(PropTool.getBoolean("droptable")){
				stat.execute("drop table if exists image");
			}
			
			stat.executeUpdate("create table if not exists image (" +
					"imageid char(20)," +
					"imageurl varchar(200), " +
					"effectiveimgindex integer(3)," +
					"downloaded integer(1)," +
					"dupped integer(1)," +
					"dupstate char(20)," +
					"viewed integer(1)," +
					"marked integer(1)," +
					"markstate char(20)," +
					"lengthmd5 varchar(80)," +
					
					"postid char(20)," +//1
					"posttitle varchar(100), " +
					"effectiveimgnum integer(3)," +	
					"viewedimgnum integer(3)," +	
					"markedimgnum integer(3)," +	
					"taged integer(1)," +	
					"processed integer(1)," +	
					"score integer(6)," +
					
					"tags varchar(20)," +//9
					"posturl varchar(100), " +
					"siteid char(10)," +
					"forumid char(10)," +
					"runid char(20)," +//23
					
					"existed boolean,"+
					"healthstate text,"+
					"statecode text,"+
					"imgindex integer(3)," +	
					"relativepath text,"+
					"width integer(6)," +
					"height integer(6)," +
					"length integer(20)," +
					"md5 integer(20)," +
					"scoretrace text,"+ //33
					
					"createtime integer(20)," +
					"modifytime integer(20)," +
					
					"listindex integer(3)," +
					"featured integer(1)" +
					
					");");
			stat.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	

	
	
	
	public synchronized static void insert(Connection conn, Image image) {
		
		String sqlstr = "insert into image values("+"?,?,?,?,?,"+"?,?,?,?,?,"+"?,?,?,?,?,"+"?,?,?,?,?,"+"?,?,?,?,?,"+"?,?,?,?,?,"+"?,?,?,?,?,?,?)";
		
		try {
			PreparedStatement prep = conn.prepareStatement(sqlstr);
			prep.setString(1,image.imageid);
			prep.setString(2,image.imageurl);
			prep.setInt(3,image.effectiveimgindex);
			prep.setBoolean(4,image.downloaded);
			prep.setBoolean(5,image.dupped);
			prep.setString(6,image.dupstate);
			prep.setBoolean(7,image.viewed);
			prep.setBoolean(8,image.marked);
			prep.setString(9,image.markstate);
			prep.setString(10,image.lengthmd5);
			
			
			prep.setString(11,image.postid);
//			prep.setString(12,image.posttitle);
//			prep.setInt(13,image.effectiveimgnum);
//			prep.setInt(14,image.viewedimgnum);
//			prep.setInt(15,image.markedimgnum);
//			prep.setBoolean(16,image.taged);
//			prep.setBoolean(17,image.processed);
//			prep.setInt(18,image.score);
//			prep.setString(19,image.tags);
//			prep.setString(20,image.posturl);
			prep.setString(21,image.siteid);
			prep.setString(22,image.forumid);
			prep.setString(23,image.runid);
			
			
			prep.setBoolean(24,image.existed);
			prep.setString(25,image.healthstate);
			prep.setString(26,image.statecode);
			prep.setInt(27,image.imgindex);
			prep.setString(28,image.relativepath);
			prep.setInt(29,image.width);
			prep.setInt(30,image.height);
			prep.setLong(31,image.length);
			prep.setString(32,image.md5);
			prep.setString(33,image.scoretrace);
			prep.setLong(34,image.createtime);
			prep.setLong(35,image.modifytime);
			
//			prep.setInt(36, image.listindex);
			prep.setBoolean(37, image.featured);
			
			
			
			prep.executeUpdate();
			
			prep.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public synchronized static void sets(ResultSet rs, Image image) throws SQLException {
		image.imageid = rs.getString("imageid");
		image.imageurl = rs.getString("imageurl");
		image.effectiveimgindex = rs.getInt("effectiveimgindex");
		image.downloaded = rs.getBoolean("downloaded");
		image.dupped = rs.getBoolean("dupped");
		image.dupstate = 	rs.getString("dupstate");
		image.viewed = rs.getBoolean("viewed");
		image.marked = rs.getBoolean("marked");
		image.markstate = rs.getString("markstate");
		image.lengthmd5 = rs.getString("lengthmd5");
		
		
		image.postid = rs.getString("postid");
		
		if(setPostColoums){
//			image.posttitle = rs.getString("posttitle");
//			image.effectiveimgnum = rs.getInt("effectiveimgnum");
//			image.viewedimgnum = rs.getInt("viewedimgnum");
//			image.markedimgnum = rs.getInt("markedimgnum");
//			image.taged = rs.getBoolean("taged");
//			image.processed = rs.getBoolean("processed");
//			image.score = rs.getInt("score");
//			image.tags = rs.getString("tags");
//			image.posturl = rs.getString("posturl");
		}
		image.siteid = rs.getString("siteid");
		image.forumid = rs.getString("forumid");
		image.runid = rs.getString("runid");
		
		
		image.existed = rs.getBoolean("existed");
		image.healthstate = rs.getString("healthstate");
		image.statecode = rs.getString("statecode");
		image.imgindex = rs.getInt("imgindex");
		image.relativepath = rs.getString("relativepath");
		image.width = rs.getInt("width");
		image.height = rs.getInt("height");
		image.length = rs.getLong("length");
		image.md5 = rs.getString("md5");
		image.scoretrace = rs.getString("scoretrace");
		image.createtime = rs.getLong("createtime");
		image.modifytime = rs.getLong("modifytime");
		
//		image.listindex = rs.getInt("listindex");
		image.featured = rs.getBoolean("featured");
		
	}


	public static void select(Connection conn, List<Image> images) {
		String sql =  "SELECT * FROM image";
		ResultSet rs;
		try {
			Statement stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while (rs.next()) {
				Image image = new Image();
				sets(rs,image);
				images.add(image);
			}
			rs.close();
			stat.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}


	public static void truncate(Connection conn) {
		String sql = "delete from image";
		Statement stat;
		try {
			stat = conn.createStatement();
			stat.executeUpdate(sql);
			stat.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void createIndexIfNotExist(Connection conn) {
		try {
			long starttime = System.currentTimeMillis();
			Statement stat = conn.createStatement();
			String sql = "CREATE INDEX  if not exists 'image-forumid' ON 'image' ('forumid')";
			stat.executeUpdate(sql);
			
			sql = "CREATE INDEX  if not exists 'image-imageid' ON 'image' ('imageid')";
			stat.executeUpdate(sql);
			
			sql = "CREATE INDEX  if not exists 'image-imageurl' ON 'image' ('imageurl')";
			stat.executeUpdate(sql);
			
			sql = "CREATE INDEX  if not exists 'image-lengthmd5' ON 'image' ('lengthmd5')";
			stat.executeUpdate(sql);
			
			sql = "CREATE INDEX  if not exists 'image-postid' ON 'image' ('postid')";
			stat.executeUpdate(sql);
			
			logger.info("create image index: " + (System.currentTimeMillis()-starttime));
			
			stat.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	static Logger logger = Logger.getLogger("");
	public static boolean setPostColoums = true;
	public static void droptable(Connection conn) {

		Statement stat;
		try {
			stat = conn.createStatement();
			stat.execute("drop table if exists image");	
			stat.close();
		} catch (SQLException e) {
			logger.info(e.getMessage());
		}
		
	}





	public static void selectWithPostFill(Connection conn,
			List<Image> images) {
		String sql =  "SELECT * FROM image";
		Map<String, Post> posts = new HashMap<String,Post>();
		
		ResultSet rs;
		try {
			
			Statement stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while (rs.next()) {
				Image image = new Image();
				sets(rs,image);
				images.add(image);
				
				String postid = image.getPostid();
				Post post = null;
				if(posts.containsKey(postid)){
					post = posts.get(postid);
				}
				else{
//					post = Application.getPostHelper().selectByPostid(postid);
					post = PostHelperDBHelper.selectByPostid(conn,postid);
					posts.put(postid, post);
				}
				image.setPost(post);
			}
			rs.close();
			stat.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

}
